<?php
/**
 * Created by PhpStorm.
 * User: Vlad
 * Date: 12/30/13
 * Time: 11:50 PM
 */

class Queries
{
    public static function GetTopGuesses($gameId, $skip, $count)
    {
        return DB::Query("	    select
                                            Object.ObjectID,
                                            Object.Name
                                from
                                            Objects as Object
                                inner join
                                            GamesInstances as Instance on Instance.ObjectID = Object.ObjectID and Instance.GameID = $gameId
                                order by
                                            Instance.Fitness desc
                                limit
                                            $skip, $count");

        //$stmt->bind_param('iii', $gameId, $skip, $count);

        //$stmt->execute();

        //return $stmt->get_result();
    }

    public static function GetGuessedRank($gameId, $objectId)
    {
        $res = DB::Query("select
                                    count(*) + 1 as `Rank`
                          from
                                    GamesInstances
                          where
                                    GameID = $gameId and
                                    Fitness > ( select
                                                          Fitness
                                                from
                                                          GamesInstances
                                                where
                                                          GameID = $gameId and
                                                          ObjectID = $objectId)");

        $stmt = $res->fetch_assoc();

        $res->close();

        return $stmt['Rank'];
    }

    public static function SetGuessedObject($gameId, $objectId)
    {
        DB::Query("     update
                                  Games
                        set
                                  GuessedObject = $objectId
                        where
                                  GameID = $gameId");
    }

    // TODO: when an object is played / guessed, show played / guessed stats for that object
    public static function IncrementGuessesForObject($objectId)
    {
        DB::Query("     update
                                  Objects
                        set
                                  TimesGuessed = TimesGuessed + 1
                        where
                                  ObjectID = $objectId");
    }

    // TODO: Also set the certainty percentage (fitness)
    public static function SetGuessedRank($gameId, $rank)
    {
        DB::Query("     update
                                  Games
                        set
                                  GuessedRank = $rank
                        where
                                  GameID = $gameId");

        if ($rank == 1)
        {
            Statistics::UpdateStat(StatType::CorrectGuessesRank1, (int)1);
        }
        else
        {
            Statistics::UpdateStat(StatType::FailedGuesses, (int)1);

            if ($rank >= 2 && $rank <= Constants::MaximumAltResults)
            {
                Statistics::UpdateStat(StatType::GuessesRank210, (int)1);
            }
            else if ($rank > Constants::MaximumAltResults)
            {
                Statistics::UpdateStat(StatType::GuessesRankOver10, (int)1);
            }
            else
            {
                Statistics::UpdateStat(StatType::GamesResultingInNewObject, (int)1);
            }
        }
    }

    public static function SetPlayedObject($gameId, $objectId)
    {
        DB::Query("     update
                                Games as g
                        inner join
                                Objects as o on o.ObjectID = $objectId and g.GameID = $gameId
                        set
                                g.PlayedObject = $objectId,
                                o.TimesPlayed = o.TimesPlayed + 1
                        where
                                GameID = $gameId");

        Statistics::UpdateStat(StatType::GamesPlayed, (int)1);
    }

    public static function InsertAndGetNewObject($name)
    {
        $stmt = DB::Prepare("insert into Objects (Name, FirstPlayed) values  (?, now())");
        $stmt->bind_param('s', $name);
        $stmt->execute();

        $stmt->close();

        $objectInsertId = DB::LastInserted();

        Statistics::UpdateStat(StatType::NumberOfObjects, (int)1);

        return $objectInsertId;
    }

    public static function GetExpectedAnswersForGame($gameId, $objectId)
    {
        $stmt = DB::Query("
                                select
                                    YesCount,
                                    NoCount,
                                    UnknownCount,
                                    ProbablyYesCount,
                                    ProbablyNoCount,
                                    greatest(	YesCount,
                                                NoCount,
                                                UnknownCount,
                                                ProbablyYesCount,
                                                ProbablyNoCount) as MostExpected
                                from
                                    GamesQuestions as GameQuestion
                                inner join
                                    Games as Game on  Game.GameID = GameQuestion.GameID and
                                                      Game.GameID = $gameId
                                inner join
                                    ObjectsQuestions as ObjectQuestion on ObjectQuestion.QuestionID = GameQuestion.QuestionID and
                                                                          ObjectQuestion.ObjectID = $objectId
                                order by
                                    GameQuestion.QuestionIndex
                            ");

        while ($row = $stmt->fetch_assoc())
        {
            switch ($row['MostExpected'])
            {
                case $row['YesCount']:
                    $answers[] = 0;
                    break;
                case $row['NoCount']:
                    $answers[] = 1;
                    break;
                case $row['UnknownCount']:
                    $answers[] = 2;
                    break;
                case $row['ProbablyYesCount']:
                    $answers[] = 3;
                    break;
                default:
                    $answers[] = 4;
                    break;
            }
        }

        $stmt->close();

        return $answers;
    }

    public static function UpdateObjectsQuestions($gameId, $multiplier, $column)
    {
        $stmt = DB::Query("    select
			                            count(*) as numQuestions
                               from
                                        GamesQuestions
                               where
                                        GameId = $gameId");
        $result = $stmt->fetch_assoc();
        $stmt->close();

        $numQuestions = $result['numQuestions'];

        // insert questions that were asked but aren't yet associated with this object
        DB::Query("
						insert ignore into
							ObjectsQuestions (ObjectID, QuestionID)
							(
								select
                                            Game.$column,
                                            GameQuestion.QuestionID
								from
                                            GamesQuestions as GameQuestion
								inner join
                                            Games as Game on Game.GameID = GameQuestion.GameID and Game.GameID = $gameId
							)
					");

        Statistics::RecomputeKnowledgeFactor();

        // update stats for the questions that were asked, but only if updating positively
        if ($multiplier > 0)
        {
            DB::Query("
                            update
                                ObjectsQuestions as ObjectQuestion
                            inner join
                                Games as Game on Game.$column = ObjectQuestion.ObjectID and Game.GameID = $gameId
                            inner join
                                GamesQuestions as GameQuestion on GameQuestion.QuestionID = ObjectQuestion.QuestionID and
                                                                  ObjectQuestion.ObjectID = Game.$column
                            set
                                ObjectQuestion.YesCount = 	case
                                                                when GameQuestion.GivenAnswer = 0 then ObjectQuestion.YesCount + 1
                                                                else ObjectQuestion.YesCount
                                                            end,
                                ObjectQuestion.NoCount = 	case
                                                                when GameQuestion.GivenAnswer = 1 then ObjectQuestion.NoCount + 1
                                                                else ObjectQuestion.NoCount
                                                            end,
                                ObjectQuestion.UnknownCount = 	case
                                                                    when GameQuestion.GivenAnswer = 2 then ObjectQuestion.UnknownCount + 1
                                                                    else ObjectQuestion.UnknownCount
                                                                end,
                                ObjectQuestion.ProbablyYesCount = 	case
                                                                        when GameQuestion.GivenAnswer = 3 then ObjectQuestion.ProbablyYesCount + 1
                                                                        else ObjectQuestion.ProbablyYesCount
                                                                    end,
                                ObjectQuestion.ProbablyNoCount = 	case
                                                                        when GameQuestion.GivenAnswer = 4 then ObjectQuestion.ProbablyNoCount + 1
                                                                        else ObjectQuestion.ProbablyNoCount
                                                                    end,
                                ObjectQuestion.Fitness = ObjectQuestion.Fitness + $multiplier * ($numQuestions - GameQuestion.QuestionIndex + 1)
                        ");
        }
    }

    public static function UpdateObjectsForInstance($gameId, $questionIndex, $answerColumn)
    {
        DB::Query("
                            update
                                GamesInstances as GameInstance
                            inner join
                                ObjectsQuestions as ObjectQuestion on ObjectQuestion.ObjectID = GameInstance.ObjectID
                            set
                                GameInstance.Fitness = GameInstance.Fitness + 2
                            where
                                GameInstance.GameID = $gameId and
                                ObjectQuestion.QuestionID = (	select
                                                                    QuestionID
                                                                from
                                                                    GamesQuestions
                                                                where
                                                                    GameID = $gameId and
                                                                    QuestionIndex = $questionIndex
                                                            ) and
                                ObjectQuestion.$answerColumn = greatest(  ObjectQuestion.YesCount,
                                                                          ObjectQuestion.NoCount,
                                                                          ObjectQuestion.UnknownCount,
                                                                          ObjectQuestion.ProbablyYesCount,
                                                                          ObjectQuestion.ProbablyNoCount)

                        "
        );
    }

    public static function UpdateLastActivity($gameId)
    {
        DB::Query(" update
                                Games
                    set
                                LastActivity = now()
                    where
                                GameID = $gameId");
    }

    public static function GetObjectsCount()
    {
        $stmt = DB::Query(" select
                                        count(*) as ObjectsCount
                            from
                                        Objects");

        $row = $stmt->fetch_assoc();

        $stmt->close();

        return $row['ObjectsCount'];
    }

    public static function GetQuestionsCount()
    {
        $stmt = DB::Query(" select
                                        count(*) as QuestionsCount
                            from
                                        Questions");

        $row = $stmt->fetch_assoc();

        $stmt->close();

        return $row['QuestionsCount'];
    }

    public static function GetObjectsQuestionsCount()
    {
        $stmt = DB::Query(" select
                                        count(*) as ObjectsQuestionsCount
                            from
                                        ObjectsQuestions");

        $row = $stmt->fetch_assoc();

        $stmt->close();

        return $row['ObjectsQuestionsCount'];
    }

    public static function GetObjectsList($page, $count)
    {
        $first = ($page - 1) * $count;
        $stmt = DB::Query(" select
                                        ObjectID, Name
                            from
                                        Objects
                            order by
                                        ObjectID
                            limit
                                        $first, $count");

        while ($row = $stmt->fetch_assoc())
        {
            $results[] = array (
                "ObjectId" => $row['ObjectID'],
                "ObjectName" => $row['Name']
            );
        }

        $stmt->close();

        return $results;
    }
}